﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Text;
using System.IO;

using PdfSharp;
using PdfSharp.Drawing;
using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;
using CE.Mail;
using CE.Image;
using CE.Global;
using CE.Database;
using CE.CreditCard;
using CE.User;
using CE.PostCode;
using CE.PersonalAccident;
using CE.Provider;
using CE.Security;
using CE.InputOut;
using CE.CarInsurance;

public partial class personal_accident_etiqa_paymentmsg : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lblMessage.Text = "";

            if (Request.Params["authresp"] != null && Request.Params["ordernumber"] != null && Request.Params["email"] != null)
            {
                string _authresp = Request.Params["authresp"].ToString();
                string _ordernumber = Request.Params["ordernumber"].ToString();
                string _authcode = "";
                string _authdt = "";
                string _cctref = "";
                string _amount = "";
                string _cemail = "";
                if (Request.Params["authcode"] != null)
                {
                    _authcode = Request.Params["authcode"].ToString();
                }

                if (Request.Params["authdt"] != null)
                {
                    _authdt = Request.Params["authdt"].ToString();
                }

                if (Request.Params["cctref"] != null)
                {
                    _cctref = Request.Params["cctref"].ToString();
                }

                if (Request.Params["amount"] != null)
                {
                    _amount = Request.Params["amount"].ToString();
                }

                _cemail = Request.Params["email"].ToString();
                string _email = Request.Params["email"].ToString();

                InsertEtiqaPaymentGateWayReply(_ordernumber, _authresp, _authcode, _authdt, _cctref, _amount, _cemail);
                ShowMessage(_authresp);
                //_authresp = "OK";
                if (_authresp == "OK")
                {
                    //_ordernumber = "20101220000000000030";

                    string CXProduct = CheckProduct(_ordernumber);

                    if (CXProduct == "PA")
                    {
                        Int64 _newPolicyNumberId = 0;
                        string _newPolicyNumber = "";
                        string _customername = "";
                        _email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_TO");
                        string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_CC");
                        string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaPA_BCC");

                        
                        Int64 _statusresult = 0;

                        EtiqaProposal _etiqaproposal = new EtiqaProposal();
                        EtiqaProposalDB _etiqaproposaldb = new EtiqaProposalDB();

                        _etiqaproposal = _etiqaproposaldb.GetPAMoreDetail(_ordernumber);

                        if (_etiqaproposal != null)
                        {
                            string convertno = General.GetRefNumber(_etiqaproposal.PAId);
                            string _fileName = "PAE" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                            string _filePath = GetFilePath();
                            //_FilePath += _fileName + ".xls";

                            _etiqaproposal.OrderNo = _ordernumber;
                            _etiqaproposal.Info10 = _filePath + _fileName + ".xls";

                            _statusresult = _etiqaproposaldb.UpdateStatus(_etiqaproposal);

                            if (_statusresult > 0)
                            {
                                UpdateConfirmationPA(_etiqaproposal);

                                string[] filelist = GeneratePDFPA(_ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);
                                if (filelist.Length == 2 && _newPolicyNumberId > 0)
                                {
                                    EtiqaPAPolicyNumberDB _policyNumberDb = new EtiqaPAPolicyNumberDB();
                                    if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                    {
                                        if (filelist[0] != "" && filelist[1] != "")
                                        {
                                            if (SendEmailWithAttachPA(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                            {
                                                Response.Redirect("paymentmsg.aspx?status=OK");
                                            }
                                            else
                                            {
                                                lblMessage.Text += "<br />Send email with attachments failed!";
                                            }
                                        }
                                        else
                                        {
                                            lblMessage.Text += "<br />PDF Files are not completed.";
                                        }
                                    }
                                    else
                                    {
                                        lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                    }
                                }
                                else
                                {
                                    lblMessage.Text += "<br />PDF Files are completed or Policy Number did not generated.";
                                }
                            }
                            else
                            {
                                lblMessage.Text = "Can't update payment status.";
                            }
                        }
                        else
                        {

                            GetUserAccounts(_ordernumber);
                            Response.Redirect("paymentmsg.aspx?status=FAIL");
                        }
                    }
                    else if (CXProduct == "CI")
                    {
                        Int64 _newPolicyNumberId = 0;
                        string _newPolicyNumber = "";
                        string _customername = "";
                        _email += ";" + CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_TO");
                        string _emailcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_CC");
                        string _emailbcc = CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaCI_BCC");

                        //_ordernumber = "20100813001478000015";
                        Int64 _statusresult = 0;
                        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
                        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
                        _etiqacarmoredetail = _etiqacarmoredetaildb.GetCarMoreDetail(_ordernumber);

                        if (_etiqacarmoredetail != null)
                        {
                            string convertno = General.GetRefNumber(_etiqacarmoredetail.CECarId);
                            string _fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
                            string _filePath = GetFilePath();
                            //_FilePath += _fileName + ".xls";

                            _etiqacarmoredetail.OrderNo = _ordernumber;
                            _etiqacarmoredetail.Info10 = _filePath + _fileName + ".xls";

                            _statusresult = _etiqacarmoredetaildb.UpdateStatus(_etiqacarmoredetail);

                            if (_statusresult > 0)
                            {
                                UpdateConfirmationCAR(_etiqacarmoredetail);

                                string[] filelist = GeneratePDFCAR(_ordernumber, out _customername, out _newPolicyNumberId, out _newPolicyNumber);
                                if (filelist.Length == 3 && _newPolicyNumberId > 0)
                                {
                                    EtiqaCarPolicyNumberDB _policyNumberDb = new EtiqaCarPolicyNumberDB();
                                    if (_policyNumberDb.UpdateIssued(_newPolicyNumberId, _ordernumber, _authcode, _authdt, _cctref, _cemail, _amount) > 0)
                                    {
                                        if (filelist[0] != "" && filelist[1] != "" && filelist[2] != "")
                                        {
                                            if (SendEmailWithAttach(filelist, _email, _emailcc, _emailbcc, _customername, _ordernumber, _newPolicyNumber))
                                            {
                                                GenerateProposalFormToEtiqa(_etiqacarmoredetail.CECarId, _filePath, _fileName);
                                                Response.Redirect("paymentmsg.aspx?status=OK");

                                            }
                                            else
                                            {
                                                lblMessage.Text += "<br />Send email with attachments failed!";
                                            }
                                        }
                                        else
                                        {
                                            lblMessage.Text += "<br />PDF Files are not completed.";
                                        }
                                    }
                                    else
                                    {
                                        lblMessage.Text += "Can't update policy number issued status. <br /> Policy Number Id =" + _newPolicyNumberId.ToString();
                                    }
                                }
                                else
                                {
                                    lblMessage.Text += "<br />PDF Files are completed or Policy Number did not generated.";
                                }
                            }
                            else
                            {
                                lblMessage.Text = "Can't update payment status.";
                            }
                        }
                        else
                        {

                            GetUserAccounts(_ordernumber);
                            Response.Redirect("paymentmsg.aspx?status=FAIL");
                        }
                    }
                }
                //end ifend
            }
            else if (Request.Params["status"] != null)
            {
                if (Request.Params["status"] == "OK")
                {
                    ShowMessage("OK");
                    lblMessage.Text += "<br />Please check your email for your Insurance documents.";
                }
                else if (Request.Params["status"] == "FAIL")
                {
                    ShowMessage("FAIL");
                }
                else
                {
                    Response.Redirect("~/home.aspx");
                }
            }
            else
            {
                Response.Redirect("~/home.aspx");
            }
        }
    }

    public bool SendEmailWithAttach(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9222 or email to us at ";
        mailBody += "<a href='mailto:cxmotor@etiqa.com.sg'>cxmotor@etiqa.com.sg</a>";
        mailBody += "<br/><br/>Marketing Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    public bool SendEmailWithAttachPA(string[] filelist, string email, string emailCC, string emailBCC, string customername, string ordernumber, string policynumber)
    {
        bool _retValue = false;

        Uri myUri = new Uri(HttpContext.Current.Request.Url.ToString());

        string domainUrl = "http://" + myUri.Host;
        string _etiqaLogoPath = domainUrl;
        _etiqaLogoPath += CE.Configuration.MyConfigurationManager.GetAppSettingValue("EtiqaLogoPath");

        string mailBody = "<br>Dear " + customername.ToUpper() + ",<br><br>";
        mailBody += "<table><tr><td><strong>Policy Number</strong></td><td>:</td><td>" + policynumber + "</td></tr>";
        mailBody += "<tr><td><strong>Payment reference</strong></td><td>:</td><td>" + ordernumber + "</td></tr></table>";
        mailBody += "<br/><br/>Thank you for insuring with Etiqa Insurance Pte Ltd.";//Etiqa Berhad
        mailBody += "<br/><br/>We are pleased to enclose the policy documents as attached for your kind attention.";
        mailBody += "<br/><br />We confirm coverage effective from the insurance commencement date as specified in ";
        mailBody += "the policy documents. If the insurance is to commence today, the time of cover starts ";
        mailBody += "from the time of this email.";
        mailBody += "<br/><br/>If you have any queries, please do not hesitate to contact us at 6331 9205 or email to us at ";
        mailBody += "<a href='mailto:cxsupport@etiqa.com.sg'>cxsupport@etiqa.com.sg</a>";
        mailBody += "<br/><br/>Marketing Dept";
        mailBody += "<br/>Etiqa Insurance Pte Ltd";
        mailBody += "<br/><img src='" + _etiqaLogoPath + "' alt='Etiqa Insurance Logo' />";
        mailBody += "<br/><br/><br/><style='font-style:italic;'>This document is automatically generated. Please do not respond to this email.</style>";

        string mailTitle = "Confirmation of Coverage with Etiqa (Policy No. " + policynumber + ")";
        if (MyMailManager.SendEtiqaEmailByAttachedmentNew(customername, email, emailCC, emailBCC, mailTitle, mailBody, filelist))
        {
            _retValue = true;
        }
        else
        {
            _retValue = false;
        }

        return _retValue;
    }

    public void ShowMessage(string AUTH)
    {
        if (AUTH == "OK")
        {
            lblMessage.Text = "Transaction successful";
        }
        else
        {
            //lblMessage.Text = "Transaction Failed. <a href = '../etiqa_proposal_form.aspx'><b>Click Here</b></a> to try again!";
            lblMessage.Text = "Transaction Failed. <a href = '../../User/ViewResultHistory.aspx'><b>Click Here</b></a> to try again!";
        }
        //else if (AUTH == "NA")
        //{
        //    lblMessage.Text = "All other host errors";
        //}
        //else if (AUTH == "IC")
        //{
        //    lblMessage.Text = "Expired card, or stolen, lost or invalid card from bank responses";
        //}
        //else if (AUTH == "RE")
        //{
        //    lblMessage.Text = "Referral";
        //}
        //else if (AUTH == "3D")
        //{
        //    lblMessage.Text = "Problem with Visa 3D was encountered such as invalid password by the customer";
        //}
        //else if (AUTH == "it")
        //{
        //    lblMessage.Text = "Fraud transaction – duplicate record with different details e.g. same OrderNumber but different amount.";
        //}
        //else if (AUTH == "TO")
        //{
        //    lblMessage.Text = "Timeout or host not available/couldn’t connect/or Host is busy doing a settlement and is not available";
        //}
        //else if (AUTH == "IT")
        //{
        //    lblMessage.Text = "Bad Merchant/Merchant invalid/invalid transaction (e.g. Amt equals to 0)";
        //}
        //else if (AUTH == "ic")
        //{
        //    lblMessage.Text = "Bad card format – card not in Bin Range for that particular merchant’s terminal.";
        //}
        //else if (AUTH == "L5" || AUTH == "L6")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that card";
        //}
        //else if (AUTH == "L4")
        //{
        //    lblMessage.Text = "Too many transactions for that card";
        //}
        //else if (AUTH == "L2" || AUTH == "L3")
        //{
        //    lblMessage.Text = "Velocity checks failed on amounts allowed for that Merchant";
        //}
        //else if (AUTH == "L1")
        //{
        //    lblMessage.Text = "Amount too large";
        //}
    }

    public void UpdateConfirmationPA(EtiqaProposal _etiqaproposal)
    {

        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "PA";
        _userconfirm.CurrentStep = 3;
   
        _userconfirm.UserSelectedId = 0;
        _userconfirm.FirstStepId = _etiqaproposal.PAId;
        _userconfirm.RefCEUserId = _etiqaproposal.RefCEUserId;
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);

    }

    public void UpdateConfirmationCAR(EtiqaCarMoreDetail _etiqacarmoredetail)
    {
        UserConfirmation _userconfirm = new UserConfirmation();
        UserConfirmationDB _userconfirmdb = new UserConfirmationDB();
        _userconfirm.ConfirmationType = "CI";
        _userconfirm.CurrentStep = 4;
        //_userconfirm.UserResultId = Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.UserSelectedId = 0;//Convert.ToInt64(Session[General.CarResultId].ToString());
        _userconfirm.FirstStepId = _etiqacarmoredetail.CECarId;//Convert.ToInt64(Session[General.CarId].ToString());
        _userconfirm.RefCEUserId = _etiqacarmoredetail.RefCEUserId;//Convert.ToInt64(Session[General.UserId].ToString());
        _userconfirm.IsConfirm = true;
        _userconfirm.ResultStatus = "Complete";
        _userconfirm.IsComplete = true;
        Int64 _result = _userconfirmdb.Insert(_userconfirm);


    }

    private void GetUserAccounts(string ORDERNO)
    {
        Int64 _UserId = 0;
        MyDatabase _db = new MyDatabase();
        string strSql = "";
        strSql = @"SELECT RefCEUserId FROM tblPAProposal_Etiqa WHERE OrderNo = " + ORDERNO;
        DataTable _dtResult = new DataTable();
        _UserId = Convert.ToInt64(_db.ExecuteScalarSqlStatement(strSql));
        Account _account = new Account();
        AccountDB _accountdb = new AccountDB();
        _account = _accountdb.GetAccount(_UserId);

        if (_account != null)
        {
            GetUserInformation(_account);
        }

        _account = null;
        _accountdb = null;
    }

    public string GetPremiumDetailsPA(string ORDERNO)
    {
        string _premium;
        _premium = "";

        DataSet _dsPremium = new DataSet();
        EtiqaProposal _etiqapaproposal = new EtiqaProposal();
        EtiqaProposalDB _etiqapaproposaldb = new EtiqaProposalDB();
        _dsPremium = _etiqapaproposaldb.GetPremiumDetails(ORDERNO);
        if (_dsPremium != null && _dsPremium.Tables[0].Rows.Count > 0)
        {
            _premium = _dsPremium.Tables[0].Rows[0]["PremiumDue"].ToString();
         
        }
       
        return _premium;
    }

    public string GetPremiumDetailsCAR(string ORDERNO)
    {
        string _premium, _totaldue, _premiumgst, _premiumdue, _annualpremium, _ncdproduct, _ncdpremium, _ncd, _basicanlpremium, _nameddr, _unnameddr, _productpercent, _ofdstatus, _ofdamount, _ofdpercent;
        _premium = ""; _totaldue = ""; _premiumgst = ""; _premiumdue = ""; _annualpremium = ""; _ncdproduct = ""; _ncdpremium = ""; _productpercent = ""; _ofdpercent = "";
        _ncd = ""; _basicanlpremium = ""; _nameddr = ""; _unnameddr = "0.00"; _ofdstatus = ""; _ofdamount = "0.00";

        DataSet _dsPremium = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        _dsPremium = _etiqacarmoredetaildb.GetPremiumDetails(ORDERNO);
        if (_dsPremium != null && _dsPremium.Tables[0].Rows.Count > 0)
        {
            _totaldue = _dsPremium.Tables[0].Rows[0]["TotalDue"].ToString();
            _premiumgst = _dsPremium.Tables[0].Rows[0]["PremiumGST"].ToString();
            _premiumdue = _dsPremium.Tables[0].Rows[0]["PremiumDue"].ToString();
            _annualpremium = _dsPremium.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _ncdproduct = _dsPremium.Tables[0].Rows[0]["NCDProduct"].ToString();
            _ncdpremium = _dsPremium.Tables[0].Rows[0]["NCDPremium"].ToString();
            _ncd = _dsPremium.Tables[0].Rows[0]["NCD"].ToString();
            _basicanlpremium = _dsPremium.Tables[0].Rows[0]["BasicAnlPremium"].ToString();

            _nameddr = _dsPremium.Tables[0].Rows[0]["DriverExessAmt"].ToString();

            Double _dNameDriverAmt = Convert.ToDouble(_nameddr);
            _unnameddr = Convert.ToString(_dNameDriverAmt * 2);

            _productpercent = _dsPremium.Tables[0].Rows[0]["ProductPercent"].ToString();
            _ofdstatus = _dsPremium.Tables[0].Rows[0]["OFDStatus"].ToString();
            _ofdamount = _dsPremium.Tables[0].Rows[0]["OFDAmount"].ToString();
            _ofdpercent = _dsPremium.Tables[0].Rows[0]["OFDPercent"].ToString();
        }
        if (_dsPremium != null && _dsPremium.Tables.Count > 1 && _dsPremium.Tables[1].Rows.Count > 0)
        {
            _unnameddr = Convert.ToSingle(_dsPremium.Tables[1].Rows[0]["ExcessAmount"].ToString()).ToString("f2");
        }
        _premium = _totaldue + "," + _premiumgst + "," + _premiumdue + "," + _annualpremium + "," + _ncdproduct + "," + _ncdpremium + "," + _ncd + "," + _basicanlpremium + "," + _nameddr + "," + _unnameddr + "," + _productpercent + "," + _ofdstatus + "," + _ofdamount + "," + _ofdpercent;
        return _premium;
    }
    
    public string[] GeneratePDFPA(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.PersonalAccident.EtiqaPAInsurancePDFOutput _etiPdfOut = new CE.PersonalAccident.EtiqaPAInsurancePDFOutput();
        string[] fileList = new string[2];
        string Premium = GetPremiumDetailsPA(ORDERNO);
        string _AgentStatus = "No";
        DataSet _dsPolicy = new DataSet();
        EtiqaProposal _etiqapaproposal = new EtiqaProposal();
        EtiqaProposalDB _etiqapaproposaldb = new EtiqaProposalDB();
        EtiqaPAPolicyNumber _policynumber = new EtiqaPAPolicyNumber();
        EtiqaPAPolicyNumberDB _policynumberdb = new EtiqaPAPolicyNumberDB();

        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";
        _dsPolicy = _etiqapaproposaldb.GetPolicyDetails(ORDERNO);


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _AgentStatus = _dsPolicy.Tables[0].Rows[0]["AgentStatus"].ToString();
            _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["PAProposalID"].ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";
            if (_AgentStatus == "Yes")
            {
                _etiPdfOut.AcountAgency = "000597/ 000597";
                _etiPdfOut.Agency = "000597";
                _etiPdfOut.Account = "000597";
            }
            else if (_AgentStatus == "No")
            {
                _etiPdfOut.AcountAgency = "000598/ 000598";
                _etiPdfOut.Agency = "000598";
                _etiPdfOut.Account = "000598";
            }
            //_etiPdfOut.AcountAgency = "000598/ 00598";
            _etiPdfOut.CardType = "";
            _etiPdfOut.CardLastFourDigit = "";

            //_etiPdfOut.Agency = "000598/000598";
            _etiPdfOut.ClassOfPolicy = "e-Protect Insurance";
            //_etiPdfOut.Account = "000598";
            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

            _etiPdfOut.TotalDueInvoice = String.Format("{0:0,0.00}", Convert.ToDouble(Premium.ToString())); 

            _etiPdfOut.PlanName = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.AccidentalDeath = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.PermanentDisability = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalParalysis = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfSight = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfSpeech = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.TotalLossOfHearingBoth = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["DeathAdult"].ToString()));
            _etiPdfOut.HospitalConfinement = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["TPDAdult"].ToString()));
            _etiPdfOut.OutpatientMedExp = String.Format("{0:0,0}", int.Parse(_dsPolicy.Tables[0].Rows[0]["MedExpAdult"].ToString()));

            _etiPdfOut.NRICNo = _dsPolicy.Tables[0].Rows[0]["NRICNo"].ToString();
            _etiPdfOut.DOB = (Convert.ToDateTime(_dsPolicy.Tables[0].Rows[0]["DOB"].ToString())).ToString("dd/MM/yyyy");
            _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["Occupation"].ToString();

            //Modified on 20 Dec 2010 by Sakthi - start
            _etiPdfOut.ChildCount = _dsPolicy.Tables[0].Rows[0]["NoOfChild"].ToString();
            _etiPdfOut.SpouseStatus = _dsPolicy.Tables[0].Rows[0]["SpouseStatus"].ToString();

            _etiPdfOut.SpouseName = "";
            _etiPdfOut.SpouseNRICNo = "";
            _etiPdfOut.SpouseDOB = "";
            _etiPdfOut.SpouseOccupation = "";

            if (_etiPdfOut.SpouseStatus == "Yes")
            {
                if (_dsPolicy.Tables[1].Rows.Count > 0)
                {
                    _etiPdfOut.SpouseName = _dsPolicy.Tables[1].Rows[0]["SpouseName"].ToString();
                    _etiPdfOut.SpouseNRICNo = _dsPolicy.Tables[1].Rows[0]["SpouseNRICNo"].ToString();
                    _etiPdfOut.SpouseDOB = (Convert.ToDateTime(_dsPolicy.Tables[1].Rows[0]["SPDOB"].ToString())).ToString("dd/MM/yyyy");
                    _etiPdfOut.SpouseOccupation = _dsPolicy.Tables[1].Rows[0]["SpouseOccupation"].ToString();
                }
            }

            _etiPdfOut.ChildName1 = "";
            _etiPdfOut.ChildNRICNo1 = "";
            _etiPdfOut.ChildDOB1 = "";

            _etiPdfOut.ChildName2 = "";
            _etiPdfOut.ChildNRICNo2 = "";
            _etiPdfOut.ChildDOB2 = "";

            _etiPdfOut.ChildName3 = "";
            _etiPdfOut.ChildNRICNo3 = "";
            _etiPdfOut.ChildDOB3 = "";

            _etiPdfOut.ChildName4 = "";
            _etiPdfOut.ChildNRICNo4 = "";
            _etiPdfOut.ChildDOB4 = "";

            _etiPdfOut.ChildName5 = "";
            _etiPdfOut.ChildNRICNo5 = "";
            _etiPdfOut.ChildDOB5 = "";
            Int16 _NoofChilds = Convert.ToInt16(_etiPdfOut.ChildCount);
            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                for (int i = 0; i < _NoofChilds; i++)
                {
                    if (i == 0)
                    {
                        _etiPdfOut.ChildName1 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo1 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB1 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 1)
                    {
                        _etiPdfOut.ChildName2 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo2 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB2 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 2)
                    {
                        _etiPdfOut.ChildName3 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo3 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB3 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 3)
                    {
                        _etiPdfOut.ChildName4 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo4 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB4 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                    if (i == 4)
                    {
                        _etiPdfOut.ChildName5 = _dsPolicy.Tables[2].Rows[i]["Name"].ToString();
                        _etiPdfOut.ChildNRICNo5 = _dsPolicy.Tables[2].Rows[i]["NRICNo"].ToString();
                        _etiPdfOut.ChildDOB5 = (Convert.ToDateTime(_dsPolicy.Tables[2].Rows[i]["DOB"].ToString())).ToString("dd/MM/yyyy");
                    }
                }
            }
            //Modified on 20 Dec 2010 by Sakthi - End
            
            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";               
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";   
        }

        return fileList;
    }

    public string[] GeneratePDFCAR(string ORDERNO, out string customername, out Int64 policyNumberId, out string policyNumber)
    {
        CE.CarInsurance.EtiqaCarInsurancePDFOutput _etiPdfOut = new CE.CarInsurance.EtiqaCarInsurancePDFOutput();
        string[] fileList = new string[3];
        string _premium = GetPremiumDetailsCAR(ORDERNO);
        string[] Premium = _premium.Split(',');
        string _cardriverList = GetCarDriverNameList(ORDERNO);
        DataSet _dsPolicy = new DataSet();
        EtiqaCarMoreDetail _etiqacarmoredetail = new EtiqaCarMoreDetail();
        EtiqaCarMoreDetailDB _etiqacarmoredetaildb = new EtiqaCarMoreDetailDB();
        EtiqaCarPolicyNumber _policynumber = new EtiqaCarPolicyNumber();
        EtiqaCarPolicyNumberDB _policynumberdb = new EtiqaCarPolicyNumberDB();
        _policynumber = _policynumberdb.GetNewPolicyNumber(30);
        policyNumberId = 0;
        policyNumber = "";
        customername = "";
        _dsPolicy = _etiqacarmoredetaildb.GetPolicyDetails(ORDERNO);


        if (_policynumber != null && _dsPolicy != null && _dsPolicy.Tables[0].Rows.Count > 0)
        {
            _etiPdfOut.ProposalId = _dsPolicy.Tables[0].Rows[0]["CECarMoreDetailId"].ToString();
            _etiPdfOut.CustomerName = _dsPolicy.Tables[0].Rows[0]["Name"].ToString();
            customername = _etiPdfOut.CustomerName;
            _etiPdfOut.CustomerAddress1 = "#" + _dsPolicy.Tables[0].Rows[0]["UnitNo"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["HomeNo"].ToString() + ", " + _dsPolicy.Tables[0].Rows[0]["Address"].ToString();
            _etiPdfOut.CustomerAddress2 = "Singapore - " + _dsPolicy.Tables[0].Rows[0]["PostalCode"].ToString();
            _etiPdfOut.PolicyNumber = _policynumber.PolicyNumber;
            policyNumberId = _policynumber.PolicyNumberId;
            policyNumber = _policynumber.PolicyNumber;
            _etiPdfOut.GSTRegNumber = "M90363266Y";
            _etiPdfOut.TransactionDate = CE.Global.General.GetUTCDateTime().ToShortDateString();

            _etiPdfOut.BranchTerritory = "SINGAPORE/ Singapore";

            _etiPdfOut.TotalAnnualPermium = _dsPolicy.Tables[0].Rows[0]["AnnualPremium"].ToString();
            _etiPdfOut.CardType = "";//_dsPolicy.Tables[0].Rows[0]["CardType"].ToString();
            _etiPdfOut.CardLastFourDigit = "";//_dsPolicy.Tables[0].Rows[0]["CreditCardNo"].ToString().Substring(8, 4);

            //_etiPdfOut.Agency = "000598"; //"001045"
            _etiPdfOut.ClassOfPolicy = "MOTOR PRIVATE CAR";
            //_etiPdfOut.Account = "000598";
            _etiPdfOut.IssuedOn = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString() + " in SINGAPORE";
            _etiPdfOut.ReplacePolicyNumber = "";
            _etiPdfOut.Client = "";
            _etiPdfOut.AcceptanceDate = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();

            _etiPdfOut.PeriodInsuranceStart = _dsPolicy.Tables[0].Rows[0]["StartDatewd"].ToString();
            _etiPdfOut.PeriodInsuranceEnd = _dsPolicy.Tables[0].Rows[0]["EndDatewd"].ToString();

            _etiPdfOut.Occupation = _dsPolicy.Tables[0].Rows[0]["NofBusi"].ToString() + " - " + _dsPolicy.Tables[0].Rows[0]["Description"].ToString();

            //Double _dBasicPremiumAmount = Convert.ToDouble(Premium[7].ToString());
            //_etiPdfOut.BasicPremiumAmount = "S$ " + _dBasicPremiumAmount.ToString("#,000.00");
            _etiPdfOut.BasicPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[7].ToString()));

            _etiPdfOut.LessNCDPercent = Premium[6].ToString() + "%";

            //Double _dLessNCDAmount = Convert.ToDouble(Premium[5].ToString());
            //_etiPdfOut.LessNCDAmount = "S$ " + _dLessNCDAmount.ToString("#,000.00"); 
            _etiPdfOut.LessNCDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[5].ToString()));

            _etiPdfOut.NCDProtectionPremiumPercent = Premium[10].ToString();

            _etiPdfOut.IsOFD = Premium[11].ToString() == "0" ? false : true;
            _etiPdfOut.LessOFDAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[12].ToString()));
            _etiPdfOut.OFDPercent = Premium[13].ToString();
            //Double _dNCDProtectionPremiumAmount = Convert.ToDouble(Premium[4].ToString());
            //_etiPdfOut.NCDProtectionPremiumAmount = "S$ " + _dNCDProtectionPremiumAmount.ToString("#,000.00");
            if (Convert.ToDouble(Premium[4].ToString()) > 0)
            {
                _etiPdfOut.IsNCDProtector = true;
            }
            else
            {
                _etiPdfOut.IsNCDProtector = false;
            }
            _etiPdfOut.NCDProtectionPremiumAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[4].ToString()));

            //_etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}",Premium[3].ToString());
            _etiPdfOut.TotalAnnualPermium = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[3].ToString()));

            //_etiPdfOut.PremiumDue = "S$ " + Premium[2].ToString();
            _etiPdfOut.PremiumDue = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[2].ToString()));
            //_etiPdfOut.PremiumGST = "S$ " + Premium[1].ToString();
            _etiPdfOut.PremiumGST = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[1].ToString()));

            //double _dtotDue = Convert.ToDouble(Premium[0].ToString());
            //_etiPdfOut.TotalDueInvoice = _dtotDue.ToString("#,000.00"); //Double S$ for Tax Invoice
            //_etiPdfOut.TotalDueSchedule = "S$ " + _dtotDue.ToString("#,000.00");

            _etiPdfOut.TotalDueInvoice = string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));
            _etiPdfOut.TotalDueSchedule = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[0].ToString()));

            _etiPdfOut.RegistrationNo = _dsPolicy.Tables[0].Rows[0]["VechicleNo"].ToString();

            _etiPdfOut.MakeModel = _dsPolicy.Tables[1].Rows[0]["Description"].ToString();
            _etiPdfOut.TypeOfCover = _dsPolicy.Tables[0].Rows[0]["PlanName"].ToString();
            _etiPdfOut.NoOfSeats = "";
            _etiPdfOut.BodyType = _dsPolicy.Tables[1].Rows[0]["Body"].ToString();
            _etiPdfOut.EngineNo = _dsPolicy.Tables[0].Rows[0]["EngineNo"].ToString();
            _etiPdfOut.Capacity = _dsPolicy.Tables[1].Rows[0]["CC"].ToString();
            _etiPdfOut.RegYear = _dsPolicy.Tables[0].Rows[0]["RegYear"].ToString();
            _etiPdfOut.ChassisNo = _dsPolicy.Tables[0].Rows[0]["ChassisNo"].ToString();
            _etiPdfOut.CertificateRef = " ";
            _etiPdfOut.DriverNameList = _cardriverList;
            _etiPdfOut.ExcessNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[8].ToString()));
            _etiPdfOut.ExcessUnNameDriverAmount = "S$ " + string.Format("{0:0,0.00}", Convert.ToDouble(Premium[9].ToString()));
            if (_dsPolicy.Tables[2].Rows.Count > 0)
            {
                _etiPdfOut.HirePurchaseCompanyName = _dsPolicy.Tables[2].Rows[0]["HPCompanyName"].ToString();
            }
            else
            {
                _etiPdfOut.HirePurchaseCompanyName = "N/A";
            }
            _etiPdfOut.CertificateNumber = _policynumber.PolicyNumber;

            if (!_etiPdfOut.GenerateAllOuput(out fileList))
            {
                fileList[0] = "";
                fileList[1] = "";
                fileList[2] = "";
            }
        }
        else
        {
            fileList[0] = "";
            fileList[1] = "";
            fileList[2] = "";
         
        }

        return fileList;
    }
    
    private string GetCarDriverNameList(string OrderNumber)
    {
        string _carDriverNameList = "";
        Int64 _UserId = 0;
        Int64 _CarId = 0;

        CarDriverDB _cardriverdb = new CarDriverDB();

        MyDatabase _db = new MyDatabase();

        string strSql = "";
        strSql = @"SELECT CECarId, RefCEUserId FROM tblCarMoreDetail_Etiqa WHERE OrderNo = " + OrderNumber;
        DataTable _dtResult = new DataTable();
        _dtResult = _db.GetDataTable(strSql);

        if (_dtResult != null && _dtResult.Rows.Count > 0)
        {
            _CarId = Convert.ToInt64(_dtResult.Rows[0][0].ToString());
            _UserId = Convert.ToInt64(_dtResult.Rows[0][1].ToString());

            DataTable _driverList = _cardriverdb.GetDriverList(_CarId, _UserId);

            if (_driverList != null && _driverList.Rows.Count > 0)
            {
                for (int i = 0; i < _driverList.Rows.Count; i++)
                {
                    if (_driverList.Rows[i]["SurName"] != null && _driverList.Rows[i]["SurName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += _driverList.Rows[i]["SurName"].ToString();
                    }

                    if (_driverList.Rows[i]["GivenName"] != null && _driverList.Rows[i]["GivenName"].ToString() != string.Empty)
                    {
                        _carDriverNameList += " " + _driverList.Rows[i]["GivenName"].ToString();
                    }

                    if ((i + 1) < _driverList.Rows.Count)
                        _carDriverNameList += ";";
                }
            }
        }
        return _carDriverNameList;
    }

    private void GetUserInformation(Account _account)
    {
        DateTime dtNow = new DateTime();
        dtNow = CE.Global.General.GetUTCDateTime();
        Session[CE.Global.General.UserId] = _account.CEID;
        Session[CE.Global.General.UserTypeId] = _account.UserTypeId;

        if (_account.UserTypeId == Convert.ToInt32(CE.Global.CEUserType.Provider))
        {
            CE.Provider.ProviderCompanyLink _comlink = new CE.Provider.ProviderCompanyLink();
            CE.Provider.ProviderCompanyLinkDB _comlinkdb = new CE.Provider.ProviderCompanyLinkDB();
            _comlink = _comlinkdb.GetProviderCompanyId(_account.CEID);
            if (_comlink != null)
            {
                Session[CE.Global.General.ProviderCompanyId] = _comlink.ProviderCompanyId;
            }
        }

        Session[CE.Global.General.UserEmail] = _account.Email;
        Session[CE.Global.General.StartTime] = dtNow.ToString();
        bool isProfileComplete = false;
        bool isContactComplete = false;

        ProfileDB _profiledb = new ProfileDB();
        Profile _profile = new Profile();
        Contact _contact = new Contact();
        ContactDB _contactdb = new ContactDB();

        _contact = _contactdb.GetContact(_account.CEID);

        _profile = _profiledb.GetProfile(_account.CEID);



        if (_contact != null)
        {
            isContactComplete = true;
            Session[CE.Global.General.UserPhoneNo] = _contact.Mobile;
        }
        else
        {
            Session[CE.Global.General.UserPhoneNo] = "No Phone No";
        }

        if (_profile != null)
        {
            if (_profile.FirstName != null && _profile.FirstName != string.Empty)
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName) + " " + Server.HtmlDecode(_profile.FirstName);
            }
            else
            {
                Session[CE.Global.General.UserName] = Server.HtmlDecode(_profile.SurName);
            }

            if (!string.IsNullOrEmpty(_profile.IDType) &&
                !string.IsNullOrEmpty(_profile.IDNo) &&
                _profile.IDNo.Length == 9)
            {
                Session[CE.Global.General.UserICNumber] = _profile.IDType + " " + _profile.IDNo;
            }

            if (_profile.Status.ToLower() == "complete")
            {
                isProfileComplete = true;
            }
            else
            {
                isProfileComplete = false;
            }
        }
        else
        {
            Session[CE.Global.General.UserName] = "No Name";
        }

        if (isProfileComplete && isContactComplete)
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "1";
        }
        else
        {
            Session[CE.Global.General.UserProfileAndContactComplete] = "0";
        }

    }

    private string GetFilePath()
    {
        string _filePath = CE.Configuration.MyConfigurationManager.GetAppSettingValue("FileOutputPath").ToString();
        _filePath += "exceloutput\\etiqa\\";
        CE.InputOut.MyIO _myIO = new CE.InputOut.MyIO();
        string _todaydate = CE.Global.General.GetUTCDateTime().ToString("yyyyMMMdd");
        if (!_myIO.CheckDirectory(_filePath + _todaydate))
        {
            if (_myIO.CreateDirectory(_filePath, _todaydate))
            {
                _filePath += _todaydate + "\\";
            }
        }
        else
        {
            _filePath += _todaydate + "\\";
        }

        return _filePath;
    }

    public Int64 InsertEtiqaPaymentGateWayReply(string _ordernumber, string _authresp, string _authcode, string _authdt, string _cctref, string _amount, string _cemail)
    {
        MyDatabase _db = new MyDatabase();
        Int64 _result = -1;
        string _spName = "sprocEtiqPaymentGateWayReplyInsertSingleItem";

        SortedList _sPara = new SortedList();

        #region Parameter

        _sPara.Add("@OrderNo", _ordernumber);
        _sPara.Add("@AuthResp", _authresp);
        _sPara.Add("@Info1", _authcode);
        _sPara.Add("@Info2", _authdt);
        _sPara.Add("@Info3", _cctref);
        _sPara.Add("@Info4", _amount);
        _sPara.Add("@Info5", _cemail);
        #endregion

        _result = _db.ExecuteSP(_spName, _sPara);

        return _result;
    }

    public string CheckProduct(string _ordernumber)
    {
        string _product = "";
        DataTable _dtResult = new DataTable("product");

        MyDatabase _db = new MyDatabase();
        string _spName = "sprocProviderEtiqaPaymentSelectProduct";

        SortedList _sPara = new SortedList();

        #region Parameter

        _sPara.Add("@OrderNo", _ordernumber);
        #endregion

        _dtResult = _db.GetDataTableBySP(_spName, _sPara);

        if (_dtResult.Rows.Count > 0)
        {
            _product = _dtResult.Rows[0]["Product"].ToString();
        }

        return _product;
    }      

    protected string GenerateProposalFormToEtiqa(Int64 RefID, string FilePath, string FileName)
    {
        string _returnFilePath = "";
        //string _filePath = FilePath;
        Label lblContent = new Label();
        //string _fileName = FileName;
        string _applicationdate = " : " + CE.Global.General.GetUTCDateTime().ToString("dd/MMM/yyyy") + " " + CE.Global.General.GetUTCDateTime().ToShortTimeString();
        //string convertno = General.GetRefNumber(RefID);
        //_fileName = "E" + convertno + "_" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;

        StringBuilder strText = new StringBuilder("");
        strText.Append("<table><tr><td colspan='2'><h4>Car Insurance - Proposal Form</h4></td><td colspan='2' align='right'><b>Application Date</b> " + _applicationdate.ToString() + "</td></tr></table>");

        DataSet _dsProposal = new DataSet();
        ProposalDownloadDB _proposaldownloaddb = new ProposalDownloadDB();
        _dsProposal = _proposaldownloaddb.GetEtiqaProposal(RefID, "CI");
        string _html = "";
        try
        {
            if (_dsProposal != null && _dsProposal.Tables[0].Rows.Count > 0)
            {
                #region Variables

                string _name, _unitno, _homeno, _address, _postalcode, _dob, _dd, _mm, _yy, _nationality, _nric, _maritalstatus, _mobile, _occupation;
                string _gender, _expdriving, _vehicleno, _make, _model, _vehicletype, _engineno, _chassisno, _cc, _regyear, _hpstatus, _hpcomp;
                string _insstartdate, _insenddate, _isdd, _ismm, _isyy, _iedd, _iemm, _ieyy, _insstatus, _inscomid, _inscomp, _inspolicyno;
                string _inspldate, _indd, _inmm, _inyy;
                string _drname1, _drdob1, _drdd1, _drmm1, _dryy1, _drocc1, _drexp1, _drname2, _drdob2, _drdd2, _drmm2, _dryy2, _drocc2, _drexp2;
                string _drname3, _drdob3, _drdd3, _drmm3, _dryy3, _drocc3, _drexp3, _drname4, _drdob4, _drdd4, _drmm4, _dryy4, _drocc4, _drexp4;
                string _drname5, _drdob5, _drdd5, _drmm5, _dryy5, _drocc5, _drexp5, _demeritstatus, _demeritcount, _dmdrname1, _dmdrpoint1;
                string _dmdrname2, _dmdrpoint2, _dmdrname3, _dmdrpoint3, _dmdrname4, _dmdrpoint4, _dmdrname5, _dmdrpoint5, _dmdrname6, _dmdrpoint6;
                string _adddriver, _drcount, _claimstatus, _claim1ststat, _claim1stend;
                string claimamt1, _claim2ndstat, _claim2ndend, claimamt2, _claim3rdstat, _claim3rdend, claimamt3, _ncdstatus, _ncdpercent;
                string _inscancelstatus, _cancelreason, _impairstatus, _impairreason, _planname, _annualpremium;
                string _excess, _email, _natureofbusi, _cxaccountno, _aucode, _policyno, _orderno, _nextNCD, _isOFD, _isNCDProtector;
                DateTime _dofb, _isdate, _iedate, _indate, _drdofb1, _drdofb2, _drdofb3, _drdofb4, _drdofb5;

                _inscancelstatus = ""; _cancelreason = ""; _impairstatus = ""; _impairreason = ""; _demeritstatus = ""; _demeritcount = "";
                _dmdrname1 = ""; _dmdrpoint1 = ""; _dmdrname2 = ""; _dmdrpoint2 = ""; _dmdrname3 = ""; _dmdrpoint3 = ""; _dmdrname4 = "";
                _dmdrpoint4 = ""; _dmdrname5 = ""; _dmdrpoint5 = ""; _dmdrname6 = ""; _dmdrpoint6 = ""; _vehicletype = ""; _expdriving = "";
                _drname1 = ""; _drdob1 = ""; _drdd1 = ""; _drmm1 = ""; _dryy1 = ""; _drocc1 = ""; _drexp1 = "";
                _drname2 = ""; _drdob2 = ""; _drdd2 = ""; _drmm2 = ""; _dryy2 = ""; _drocc2 = ""; _drexp2 = "";
                _drname3 = ""; _drdob3 = ""; _drdd3 = ""; _drmm3 = ""; _dryy3 = ""; _drocc3 = ""; _drexp3 = "";
                _drname4 = ""; _drdob4 = ""; _drdd4 = ""; _drmm4 = ""; _dryy4 = ""; _drocc4 = ""; _drexp4 = "";
                _drname5 = ""; _drdob5 = ""; _drdd5 = ""; _drmm5 = ""; _dryy5 = ""; _drocc5 = ""; _drexp5 = ""; _planname = ""; _annualpremium = "";
                _adddriver = ""; _drcount = "";

                #endregion

                #region Assign Values

                _name = _dsProposal.Tables[0].Rows[0]["Name"].ToString();
                _dofb = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["DOB"].ToString());
                _dd = _dofb.Day.ToString("00");
                _mm = _dofb.Month.ToString("00");
                _yy = _dofb.Year.ToString();
                //_dob = _dd + "/" + _mm + "/" + _yy;
                _dob = _dsProposal.Tables[0].Rows[0]["DOB"].ToString();
                _nationality = _dsProposal.Tables[0].Rows[0]["Nationality"].ToString();
                _unitno = _dsProposal.Tables[0].Rows[0]["UnitNo"].ToString();
                _homeno = _dsProposal.Tables[0].Rows[0]["HomeNo"].ToString();
                _address = _dsProposal.Tables[0].Rows[0]["Address"].ToString();
                _postalcode = _dsProposal.Tables[0].Rows[0]["PostalCode"].ToString();
                _nric = _dsProposal.Tables[0].Rows[0]["NRICNo"].ToString();
                _maritalstatus = _dsProposal.Tables[0].Rows[0]["Marital"].ToString();
                _gender = _dsProposal.Tables[0].Rows[0]["Gender"].ToString();

                _email = _dsProposal.Tables[0].Rows[0]["Email"].ToString();
                _excess = _dsProposal.Tables[0].Rows[0]["Excess"].ToString();
                _natureofbusi = _dsProposal.Tables[0].Rows[0]["NofBusi"].ToString();
                _nextNCD = _dsProposal.Tables[0].Rows[0]["NCDNextPercent"].ToString();
                _isNCDProtector = _dsProposal.Tables[0].Rows[0]["IsNCDProtector"].ToString(); ;
                _isOFD = _dsProposal.Tables[0].Rows[0]["IsOffDriveCom"].ToString();

                _planname = _dsProposal.Tables[0].Rows[0]["PlanName"].ToString();
                _annualpremium = _dsProposal.Tables[0].Rows[0]["AnnualPremium"].ToString();

                _expdriving = _dsProposal.Tables[0].Rows[0]["YearDriveSg"].ToString();
                _occupation = _dsProposal.Tables[0].Rows[0]["Description"].ToString();
                _mobile = _dsProposal.Tables[0].Rows[0]["ContactNo"].ToString();
                if (_dsProposal.Tables[0].Rows[0]["IsClaimLastThreeYear"].ToString() == "False")
                {
                    _claimstatus = "No";
                }
                else
                {
                    _claimstatus = "Yes";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasPreInsurer"].ToString() == "True")
                {
                    _insstatus = "Yes";
                    if (_dsProposal.Tables[2].Rows.Count > 0)
                    {
                        _inscomp = _dsProposal.Tables[2].Rows[0]["InsCompanyName"].ToString();
                        _inspolicyno = _dsProposal.Tables[0].Rows[0]["PreInsPolicyNo"].ToString();
                        _indate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString());
                        _indd = _indate.Day.ToString("00");
                        _inmm = _indate.Month.ToString("00");
                        _inyy = _indate.Year.ToString();
                        _inspldate = _indd + "/" + _inmm + "/" + _inyy;
                        //_inspldate = _dsProposal.Tables[0].Rows[0]["PreInsExpiryDate"].ToString();
                        _inscomid = _dsProposal.Tables[0].Rows[0]["PreInsurerName"].ToString();
                    }
                    else
                    {
                        _insstatus = "No";
                        _inscomp = "Not Applicable";
                        _inspolicyno = "";
                        _inspldate = "";
                        _inscomid = "01";
                    }
                }
                else
                {
                    _insstatus = "No";
                    _inscomp = "Not Applicable";
                    _inspolicyno = "";
                    _inspldate = "";
                    _inscomid = "01";
                }


                _cxaccountno = _dsProposal.Tables[0].Rows[0]["CXAccountNo"].ToString();
                _policyno = _dsProposal.Tables[0].Rows[0]["PolicyNumber"].ToString();
                _aucode = _dsProposal.Tables[0].Rows[0]["AuCode"].ToString();
                _orderno = _dsProposal.Tables[0].Rows[0]["OrderNo"].ToString();


                _ncdpercent = _dsProposal.Tables[0].Rows[0]["NCDPercent"].ToString();
                if (_ncdpercent.ToString() == "-1")
                {
                    _ncdpercent = "No NCD(First Policy)";
                }
                else
                {
                    _ncdpercent += "%";
                }

                if (_dsProposal.Tables[1].Rows.Count > 0)
                {
                    _claim1ststat = _dsProposal.Tables[1].Rows[0]["Claim1stStartDate"].ToString();
                    _claim1stend = _dsProposal.Tables[1].Rows[0]["Claim1stEndDate"].ToString();
                    claimamt1 = _dsProposal.Tables[1].Rows[0]["NOCLast1Value"].ToString();

                    _claim2ndstat = _dsProposal.Tables[1].Rows[0]["Claim2ndStartDate"].ToString();
                    _claim2ndend = _dsProposal.Tables[1].Rows[0]["Claim2ndEndDate"].ToString();
                    claimamt2 = _dsProposal.Tables[1].Rows[0]["NOCLast2Value"].ToString();

                    _claim3rdstat = _dsProposal.Tables[1].Rows[0]["Claim3rdStartDate"].ToString();
                    _claim3rdend = _dsProposal.Tables[1].Rows[0]["Claim3rdEndDate"].ToString();
                    claimamt3 = _dsProposal.Tables[1].Rows[0]["NOCLast3Value"].ToString();
                }
                else
                {
                    _claim1ststat = "";
                    _claim1stend = "";
                    claimamt1 = "";

                    _claim2ndstat = "";
                    _claim2ndend = "";
                    claimamt2 = "";

                    _claim3rdstat = "";
                    _claim3rdend = "";
                    claimamt3 = "";
                    _claimstatus = "No";
                }
                if (_dsProposal.Tables[0].Rows[0]["HasInsuranceTerminated"].ToString() == "True")
                {
                    _inscancelstatus = "Yes";
                    _cancelreason = _dsProposal.Tables[0].Rows[0]["InsuranceTerminatedReason"].ToString();
                }
                else
                {
                    _inscancelstatus = "No";
                    _cancelreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasPhysicalImpairment"].ToString() == "True")
                {
                    _impairstatus = "Yes";
                    _impairreason = _dsProposal.Tables[0].Rows[0]["PhysicalImpairmentReason"].ToString();
                }
                else
                {
                    _impairstatus = "No";
                    _impairreason = "";
                }

                if (_dsProposal.Tables[0].Rows[0]["HasDemeritPoints"].ToString() == "True")
                {
                    if (_dsProposal.Tables[6].Rows.Count > 0)
                    {
                        _demeritstatus = "Yes";
                        _demeritcount = _dsProposal.Tables[6].Rows.Count.ToString();

                        _dmdrname1 = _dsProposal.Tables[6].Rows[0]["DriverName"].ToString();
                        _dmdrpoint1 = _dsProposal.Tables[6].Rows[0]["TotalDemeritPoints"].ToString();
                        if (_dsProposal.Tables[6].Rows.Count > 1)
                        {
                            _dmdrname2 = _dsProposal.Tables[6].Rows[1]["DriverName"].ToString();
                            _dmdrpoint2 = _dsProposal.Tables[6].Rows[1]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 2)
                        {
                            _dmdrname3 = _dsProposal.Tables[6].Rows[2]["DriverName"].ToString();
                            _dmdrpoint3 = _dsProposal.Tables[6].Rows[2]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 3)
                        {
                            _dmdrname4 = _dsProposal.Tables[6].Rows[3]["DriverName"].ToString();
                            _dmdrpoint4 = _dsProposal.Tables[6].Rows[3]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 4)
                        {
                            _dmdrname5 = _dsProposal.Tables[6].Rows[4]["DriverName"].ToString();
                            _dmdrpoint5 = _dsProposal.Tables[6].Rows[4]["TotalDemeritPoints"].ToString();
                        }
                        if (_dsProposal.Tables[6].Rows.Count > 5)
                        {
                            _dmdrname6 = _dsProposal.Tables[6].Rows[5]["DriverName"].ToString();
                            _dmdrpoint6 = _dsProposal.Tables[6].Rows[5]["TotalDemeritPoints"].ToString();
                        }
                    }
                    else
                    {
                        _demeritstatus = "No";
                        _demeritcount = "0";
                    }
                }
                else
                {
                    _demeritstatus = "No";
                    _demeritcount = "0";
                }

                _vehicleno = _dsProposal.Tables[0].Rows[0]["VechicleNo"].ToString();
                _regyear = _dsProposal.Tables[0].Rows[0]["RegYear"].ToString();
                _model = _dsProposal.Tables[5].Rows[0]["Description"].ToString();
                _make = _dsProposal.Tables[5].Rows[0]["Make"].ToString(); ;
                _cc = _dsProposal.Tables[5].Rows[0]["CC"].ToString();
                _vehicletype = _dsProposal.Tables[5].Rows[0]["Body"].ToString();
                _engineno = _dsProposal.Tables[0].Rows[0]["EngineNo"].ToString();
                _chassisno = _dsProposal.Tables[0].Rows[0]["ChassisNo"].ToString();

                _isdate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["StartDate"].ToString());
                _isdd = _isdate.Day.ToString("00");
                _ismm = _isdate.Month.ToString("00");
                _isyy = _isdate.Year.ToString();
                _insstartdate = _isdd + "/" + _ismm + "/" + _isyy;
                //_insstartdate = _dsProposal.Tables[0].Rows[0]["StartDate"].ToString();

                _iedate = Convert.ToDateTime(_dsProposal.Tables[0].Rows[0]["EndDate"].ToString());
                _iedd = _iedate.Day.ToString("00");
                _iemm = _iedate.Month.ToString("00");
                _ieyy = _iedate.Year.ToString();
                _insenddate = _iedd + "/" + _iemm + "/" + _ieyy;
                //_insenddate = _dsProposal.Tables[0].Rows[0]["EndDate"].ToString();

                if (_dsProposal.Tables[3].Rows.Count > 0)
                {
                    _hpstatus = "Yes";
                    _hpcomp = _dsProposal.Tables[3].Rows[0]["HPCompanyName"].ToString();

                    if (_hpcomp.ToString() == "")
                    {
                        _hpcomp = "Not Applicable";
                    }
                }
                else
                {
                    _hpstatus = "No";
                    _hpcomp = "Not Applicable";
                }
                if (_dsProposal.Tables[0].Rows[0]["AddDriver"].ToString() == "Yes")
                {
                    _adddriver = "Yes";
                    _drcount = _dsProposal.Tables[0].Rows[0]["NoOfDriver"].ToString();
                }
                else
                {
                    _adddriver = "No";
                    _drcount = "0";
                }
                if (_dsProposal.Tables[4].Rows.Count > 0)
                {
                    if (Convert.ToInt16(_drcount) > 0)
                    {
                        _drname1 = _dsProposal.Tables[4].Rows[0]["DriverName"].ToString();
                        _drdofb1 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[0]["DOB"].ToString());
                        _drdd1 = _drdofb1.Day.ToString("00");
                        _drmm1 = _drdofb1.Month.ToString("00");
                        _dryy1 = _drdofb1.Year.ToString();
                        //_drdob1 = _drdd1 + "/" + _drmm1 + "/" + _dryy1;
                        _drdob1 = _dsProposal.Tables[4].Rows[0]["DOB"].ToString();
                        _drocc1 = _dsProposal.Tables[4].Rows[0]["Description"].ToString();
                        _drexp1 = _dsProposal.Tables[4].Rows[0]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 1)
                    {
                        _drname2 = _dsProposal.Tables[4].Rows[1]["DriverName"].ToString();
                        _drdofb2 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[1]["DOB"].ToString());
                        _drdd2 = _drdofb2.Day.ToString("00");
                        _drmm2 = _drdofb2.Month.ToString("00");
                        _dryy2 = _drdofb2.Year.ToString();
                        //_drdob2 = _drdd2 + "/" + _drmm2 + "/" + _dryy2;
                        _drdob2 = _dsProposal.Tables[4].Rows[1]["DOB"].ToString();
                        _drocc2 = _dsProposal.Tables[4].Rows[1]["Description"].ToString();
                        _drexp2 = _dsProposal.Tables[4].Rows[1]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 2)
                    {
                        _drname3 = _dsProposal.Tables[4].Rows[2]["DriverName"].ToString();
                        _drdofb3 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[2]["DOB"].ToString());
                        _drdd3 = _drdofb3.Day.ToString("00");
                        _drmm3 = _drdofb3.Month.ToString("00");
                        _dryy3 = _drdofb3.Year.ToString();
                        //_drdob3 = _drdd3 + "/" + _drmm3 + "/" + _dryy3;
                        _drdob3 = _dsProposal.Tables[4].Rows[2]["DOB"].ToString();
                        _drocc3 = _dsProposal.Tables[4].Rows[2]["Description"].ToString();
                        _drexp3 = _dsProposal.Tables[4].Rows[2]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 3)
                    {
                        _drname4 = _dsProposal.Tables[4].Rows[3]["DriverName"].ToString();
                        _drdofb4 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[3]["DOB"].ToString());
                        _drdd4 = _drdofb4.Day.ToString("00");
                        _drmm4 = _drdofb4.Month.ToString("00");
                        _dryy4 = _drdofb4.Year.ToString();
                        //_drdob4 = _drdd4 + "/" + _drmm4 + "/" + _dryy4;
                        _drdob4 = _dsProposal.Tables[4].Rows[3]["DOB"].ToString();
                        _drocc4 = _dsProposal.Tables[4].Rows[3]["Description"].ToString();
                        _drexp4 = _dsProposal.Tables[4].Rows[3]["YearOfDrivingInSg"].ToString();
                    }
                    if (Convert.ToInt16(_drcount) > 4)
                    {
                        _drname5 = _dsProposal.Tables[4].Rows[4]["DriverName"].ToString();
                        _drdofb5 = Convert.ToDateTime(_dsProposal.Tables[4].Rows[4]["DOB"].ToString());
                        _drdd5 = _drdofb5.Day.ToString("00");
                        _drmm5 = _drdofb5.Month.ToString("00");
                        _dryy5 = _drdofb5.Year.ToString();
                        //_drdob5 = _drdd5 + "/" + _drmm5 + "/" + _dryy5;
                        _drdob5 = _dsProposal.Tables[4].Rows[4]["DOB"].ToString();
                        _drocc5 = _dsProposal.Tables[4].Rows[4]["Description"].ToString();
                        _drexp5 = _dsProposal.Tables[4].Rows[4]["YearOfDrivingInSg"].ToString();
                    }
                }

                #endregion

                #region Excel Generation

                //_html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr></table>";
                //_html += "<div><div><b>Details of Proposer</b></div><div style='text-align: center'>"
                _html = "<table><tr><td width='25%'><b>Plan Name</b></td><td>" + _planname + "</td><td colspan='2' align='right'><b>Annual Premium</b> : $" + _annualpremium.ToString() + "</td></tr>";
                _html += "<tr><td colspan='2'><b>Details of Proposer</b></td><td colspan='2' align='right'><b>Excess Amount</b> : $" + _excess.ToString() + "</td></tr></table>";
                _html += "<div><div style='text-align: center'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"
                         + "<tr>"
                         + "<td width='50%'>" //Start 1
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Name</td>"
                         + "<td width='50%' align='left'>" + _name.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td align='left'>Address</td>"
                         + "<td align='left'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0'>"
                         + "<tr><td align='left'>#" + _unitno.ToString() + " - " + _homeno.ToString() + "</td></tr>"
                         + "<tr><td align='left'>" + _address.ToString() + "</td></tr>"
                         + "<tr><td align='left'>Postal Code " + _postalcode.ToString() + "</td></tr>"
                         + "</table>"
                         + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Email</td>"
                         + "<td width='50%' align='left'>" + _email.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Occupation / Business</td>"
                         + "<td width='50%' align='left'>" + _occupation.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Contact No</td>"
                         + "<td width='50%' align='left'>" + _mobile.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Existing NCD</td>"
                         + "<td width='50%' align='left'>" + _ncdpercent.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>OFD</td>"
                         + "<td width='50%' align='left'>" + _isOFD.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 1

                         + "<td width='50%'>" //Start 2
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Date of Birth</td>"
                         + "<td width='50%' style='mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _dob.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nationality</td>"
                         + "<td width='50%' align='left'>" + _nationality.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>NRIC Number</td>"
                         + "<td width='50%' align='left'>" + _nric.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Sex</td>"
                         + "<td width='50%' align='left'>" + _gender.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Marital Status</td>"
                         + "<td width='50%' align='left'>" + _maritalstatus.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Nature of Business</td>"
                         + "<td width='50%' align='left'>" + _natureofbusi.ToString() + "</td>"
                         + "</tr>"

                         + "<tr>"
                         + "<td width='50%' align='left'>Driving Experience</td>"
                         + "<td width='50%' align='left'>" + _expdriving.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>NCD upon renewal</td>"
                         + "<td width='50%' align='left'>" + _nextNCD.ToString() + "</td>"
                         + "</tr>"

                          + "<tr>"
                         + "<td width='50%' align='left'>Is NCDProtector</td>"
                         + "<td width='50%' align='left'>" + _isNCDProtector.ToString() + "</td>"
                         + "</tr>"

                         + "</table>"
                         + "</td>" //End 2
                         + "</tr>"

                         + "<tr>"
                         + "<td colspan='2'>"
                         + "<table style='width: 100%; background-color: white;' cellpadding='0' cellspacing='0' border='1'>"

                         + "<tr><td colspan='4' align='left'><b>Particulars of Vehicle</b></td></tr>"

                         + "<tr><td style='width:25%' align='left'>Registration No</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicleno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Year of Registration</td>"
                         + "<td style='width: 25%' align='left'>" + _regyear.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Make of Vehicle</td>"
                         + "<td style='width: 25%' align='left'>" + _make.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Vehicle Model</td>"
                         + "<td style='width: 25%' align='left'>" + _model.ToString() + "</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Vehicle Type</td>"
                         + "<td style='width: 25%' align='left'>" + _vehicletype.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Engine capacity</td>"
                         + "<td style='width: 25%' align='left'>" + _cc.ToString() + "cc</td></tr>"

                         + "<tr><td style='width:25%' align='left'>Engine No</td>"
                         + "<td style='width: 25%' align='left'>" + _engineno.ToString() + "</td>"
                         + "<td style='width: 25%' align='left'>Chassis No</td>"
                         + "<td style='width: 25%' align='left'>" + _chassisno.ToString() + "</td></tr>"

                         + "<tr><td colspan='4' align='left'><b>Additional drivers : </b>" + _adddriver.ToString() + "</td></tr>";

                if (Convert.ToInt16(_drcount) > 0)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 1</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob1.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 1)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 2</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob2.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 2)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 3</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob3.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 3)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 4</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob4.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_drcount) > 4)
                {
                    _html += "<tr><td colspan='4' align='left'>Driver 5</td></tr>"
                    + "<tr><td style='width:25%' align='left'>Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _drname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Date of Birth</td>"
                    + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _drdob5.ToString() + "</td></tr>"

                    + "<tr><td style='width:25%' align='left'>Occupation</td>"
                    + "<td style='width: 25%' align='left'>" + _drocc5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Driving Experience</td>"
                    + "<td style='width: 25%' align='left'>" + _drexp5.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='4' align='left'>Period of insurance</td></tr>"

                + "<tr><td style='width:25%' align='left'>From</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insstartdate.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>To</td>"
                + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _insenddate.ToString() + "</td></tr>"

                + "<tr><td style='width:25%' align='left'>Is this vehicle under Hire Purchase?</td>"
                + "<td style='width: 25%' align='left'>" + _hpstatus.ToString() + "</td>"
                + "<td style='width: 25%' align='left'>Name of finance Company</td>"
                + "<td style='width: 25%' align='left'>" + _hpcomp.ToString() + "</td></tr>";

                if (_insstatus == "Yes" && _inscomid != "01" && _inscomid != "177")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>"

                            + "<tr><td style='width:25%' align='left'>Policy No.</td>"
                            + "<td style='width:25%' align='left'>" + _inspolicyno.ToString() + "</td>"
                            + "<td style='width: 25%' align='left'>Expiry Date</td>"
                            + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _inspldate.ToString() + "</td></tr>";
                }
                else
                {
                    _html += "<tr><td style='width: 25%' align='left'>Current Insurance Company</td>"
                            + "<td colspan='3' align='left'>" + _inscomp.ToString() + "</td></tr>";
                }
                _html += "<tr>"
                + "<td colspan='4' align='left'>Claim history o proposer & named / Authorised Driver(s)(Last 3 years) : " + _claimstatus.ToString() + "</td>"
                + "</tr>";
                if (_claimstatus.ToString() == "Yes")
                {
                    if (claimamt1.ToString() != "0" && claimamt1.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width:25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'> " + _claim1ststat.ToString() + " to " + _claim1stend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt1.ToString() + "</td></tr>";
                    }
                    if (claimamt2.ToString() != "0" && claimamt2.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim2ndstat.ToString() + " to " + _claim2ndend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt2.ToString() + "</td></tr>";
                    }
                    if (claimamt3.ToString() != "0" && claimamt3.ToString() != "")
                    {
                        _html += "<tr><td style='width:25%' align='left'>Claim Date</td>"
                        + "<td style='width: 25%; mso-number-format:dd\\-mmm\\-yyyy' align='left'>" + _claim3rdstat.ToString() + " to " + _claim3rdend.ToString() + "</td>"
                        + "<td style='width: 25%' align='left'> Claim Amount</td>"
                        + "<td style='width: 25%' align='left'>" + claimamt3.ToString() + "</td></tr>";
                    }
                }
                _html += "<tr><td colspan='4' align='left'><b>General Questions</td></tr>"

                + "<tr><td colspan='3' align='left'>Have you or your named driver(s) been given / accumulated demerit points during the last 24 months?</td>"
                + "<td style='width: 25%' align='left'>" + _demeritstatus.ToString() + "</td></tr>";
                if (Convert.ToInt16(_demeritcount) > 0)
                {
                    _html += "<tr><td style='width:25%' align='left'>1. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname1.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint1.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 1)
                {
                    _html += "<tr><td style='width:25%' align='left'>2. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname2.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint2.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 2)
                {
                    _html += "<tr><td style='width:25%' align='left'>3. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname3.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint3.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 3)
                {
                    _html += "<tr><td style='width:25%' align='left'>4. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname4.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint4.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 4)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname5.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint5.ToString() + "</td></tr>";
                }
                if (Convert.ToInt16(_demeritcount) > 5)
                {
                    _html += "<tr><td style='width:25%' align='left'>5. Driver Name</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrname6.ToString() + "</td>"
                    + "<td style='width: 25%' align='left'>Total Demerit Points</td>"
                    + "<td style='width: 25%' align='left'>" + _dmdrpoint6.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) had any motor insurance proposal declined, cancelled or renewal rejected by any insurance company?</td>"
                + "<td style='width: 25%' align='left'>" + _inscancelstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _cancelreason.ToString() + "</td></tr>";
                }
                _html += "<tr><td colspan='3' align='left'>Have you or your named driver(s) suffered any disease or infirmity that could impair the ability to drive?</td>"
                        + "<td style='width: 25%' align='left'>" + _impairstatus.ToString() + "</td></tr>";
                if (_inscancelstatus.ToString() == "Yes")
                {
                    _html += "<tr><td style='width: 25%' align='left'>Reason</td>"
                    + "<td colspan='3' align='left'>" + _impairreason.ToString() + "</td></tr>";
                }

                _html += "<tr><td colspan='4' align='left'></td></tr>";
                _html += "<tr><td>Account No.</td><td>[" + _cxaccountno + "]</td><td>Policy No.</td><td>[" + _policyno + "]</td></tr>";
                _html += "<tr><td>Order No.</td><td>[" + _orderno + "]</td><td>AuCode</td><td>[" + _aucode + "]</td></tr>";

                _html += "</table>"
                + "</td>"
                + "</tr>"

                + "</table>"
                + "</div></div>";

                #endregion

                lblContent.Text = _html;
                MyIO myio = new MyIO();

                string[] filecontent = new string[1];
                filecontent[0] = strText + lblContent.Text.ToString();

                //_filePath = GetFilePath();
                myio.WriteNewFile(FilePath, FileName + ".xls", filecontent);
                _returnFilePath = FilePath + FileName + ".xls";
                ////SEND MAIL
              
            }
        }
        catch (Exception Ex)
        {
            Ex = null;
        }

        return _returnFilePath;

    }

}
